package com.cplatform.movie.back.store.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cplatform.dbhelp.DbHelper;
import com.cplatform.dbhelp.page.Page;
import com.cplatform.movie.back.dao.AuditStepDao;
import com.cplatform.movie.back.entity.AuditStep;
import com.cplatform.movie.back.service.BsFileService;
import com.cplatform.movie.back.store.dao.StoreDao;
import com.cplatform.movie.back.store.entity.Store;
import com.cplatform.movie.back.utils.ExcelExportUtil;
import com.cplatform.movie.back.utils.LogUtils;
import com.cplatform.movie.back.utils.data.RoleDataUtils;

/**
 * Title.商户管理相关 <br>
 * Description.
 * <p>
 * Copyright: Copyright (c) 2013-5-28 下午4:34:36
 * <p>
 * Version: 1.0
 * <p>
 */
@Service
public class StoreService {

	private static final Logger log = Logger.getLogger(StoreService.class);

	@Autowired
	private DbHelper dbHelper;

	@Autowired
	private BsFileService bsFileService;

	@Autowired
	private StoreDao storeDao;

	@Autowired
	private AuditStepDao stepDao;


	@Autowired
	private LogUtils logUtils;

	private final Long AUDIT_PASS_STATUS = 3L;

	private final Long STORE = 2L;

	private final Long CHANNEL = 3L;


	public Store findStoreById(Long id) {
		return this.storeDao.findOne(id);
	}

	public Store saveStore(Store store) {
		return this.storeDao.save(store);
	}


	public List<Store> findStoreListByName(String name) {
		return this.storeDao.findByName(name);
	}

	/**
	 * 查询商户分页
	 * 
	 * @param terminalId
	 *            要查询的手机号码
	 * @param pageNo
	 *            页面
	 * @param pageSize
	 *            每页显示
	 * @return
	 * @throws SQLException
	 */
	public Page<Store> listStore(Store store, int pageNo, int pageSize, String status, String ids) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append(" select store.id,store.AREA_CODE,store.ADDRESS, ");
		sql.append(" store.create_time,us.user_code as userName, store.NAME,store.STATUS,region.region_name areaName from t_store store, ");
		sql.append(" t_sys_region region, t_sys_user us");
		sql.append(" where  store.is_valid=1  and store.area_code = region.region_code(+) and store.sys_user_id=us.id(+) ");
		List<Object> params = new ArrayList<Object>();

		if (StringUtils.isNotBlank(status)) {
			sql.append(" and store.status in ( ").append(status).append(")");
		}

		if (StringUtils.isNotBlank(ids)) {
			sql.append(" and store.id not in ( ").append(ids).append(" )");
		}

		if (StringUtils.isNotBlank(store.getBeginTime())) {
			sql.append(" and store.create_time >= ? ");
			params.add(store.getBeginTime() + "000000");
		}
		if (StringUtils.isNotBlank(store.getEndTime())) {
			sql.append(" and store.create_time <= ? ");
			params.add(store.getEndTime() + "235959");
		}

		if (store.getIsValid() != null) {
			sql.append(" and store.is_valid = ? ");
			params.add(store.getIsValid());
		}

		if (store.getId() != null) {
			sql.append(" and store.id = ? ");
			params.add(store.getId());
		}

		if (StringUtils.isNotBlank(store.getName())) {
			sql.append(" and store.name like ? ");
			params.add("%" + store.getName().trim() + "%");
		}

		if (StringUtils.isNotBlank(store.getAreaName())) {
			sql.append("  and (region.region_name like ? or region.parent_region in (select pr.region_code from t_sys_region pr where pr.region_name like ?)) ");
			params.add("%" + store.getAreaName().trim() + "%");
			params.add("%" + store.getAreaName().trim() + "%");
		}

		if (StringUtils.isNotBlank(store.getStatus() == null ? "" : store.getStatus().toString())) {
			sql.append(" and store.status = ? ");
			params.add(store.getStatus());
		}

		// 获取已同步商户end
		sql.append(RoleDataUtils.getRoleData(RoleDataUtils.MODULE_STORE));
		sql.append(" order by store.create_time desc ");
		return dbHelper.getPage(sql.toString(), Store.class, pageNo, pageSize, params.toArray());
	}

	/**
	 * 设置store相关属性
	 * 
	 * @param storePage
	 * @return
	 * @throws SQLException
	 */
	public Page<Store> setStorePage(Page<Store> storePage) throws SQLException {
		if (null != storePage && storePage.getData().size() > 0) {
			for (int i = 0; i < storePage.getData().size(); i++) {
				Store store = storePage.getData().get(i);
				List<AuditStep> auditStepList = findShopAuditSteplist(store.getId());
				if (null != auditStepList && auditStepList.size() > 0) {
					// 获取最新审核时间
					store.setAuditTime(auditStepList.get(auditStepList.size() - 1).getUpdateTime());
				}
			}
		}
		return storePage;
	}

	/**
	 * 保存商户
	 * 
	 * @param store
	 *            商户实体类
	 * @param is
	 *            文件流
	 * @param list
	 *            选择的渠道商列表
	 * @param opType
	 *            操作类型 true 修改 false 增加
	 */

	@Transactional
	public Store saveUpdateStore(Store store) {
		// 保存store表
		storeDao.save(store);
		return store;

	}


	/**
	 * 根据条件获取商户列表
	 * 
	 * @param filter
	 * @return
	 * @throws SQLException
	 */
	public List<Store> getStoreListByFilter(String filter) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.setLength(0);
		sql.append("select * from t_store t where t.id in ( ").append(filter).append(" )");
		return dbHelper.getBeanList(sql.toString(), Store.class, null);

	}

	/**
	 * 保存商户
	 * 
	 * @param store
	 *            商户实体类
	 * @param is
	 *            文件流
	 * @param list
	 *            选择的渠道商列表
	 * @param opType
	 *            操作类型 true 修改 false 增加
	 * @throws SQLException
	 */

	@Transactional
	public void updateStoreSatus(Store store, AuditStep step) throws SQLException {

		// try {
		storeDao.save(store);
		dbHelper.execute("update t_store_agent set status=? where js_store_id=?", new Object[] { store.getStatus(), store.getId() });
		stepDao.save(step);
		if (null != step && store.getStatus() == 3) {
			// 生成商户
//			shopUserService.saveShopUser(2L, store.getId(), 1L);
		}
		// }
		// catch (SQLException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }

	}

	/**
	 * 审核非结算商户
	 * 
	 * @param store
	 * @param step
	 * @return
	 */
	@Transactional
	public String auditNoneStore(Store store, AuditStep step) {
		String msg = "操作成功";
		saveStore(store);
		stepDao.save(step);
		return msg;
	}


	public List<AuditStep> findShopAuditSteplist(Long id) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql.append("select t.update_time,t.remark,t.status from t_audit_step t where t.BS_TABEL = 't_store' and t.BS_ID = ? order by t.UPDATE_TIME");
		List<Object> paramsList = new ArrayList<Object>();
		paramsList.add(id);
		return dbHelper.getBeanList(sql.toString(), AuditStep.class, paramsList.toArray());
	}


	public void exportStoreToExcel(Store store, HttpServletRequest request, HttpServletResponse response) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append(" select store.name, ");
		sql.append("        case store.operate_class ");
		sql.append("          when 1 then ");
		sql.append("           '居家生活' ");
		sql.append("          when 2 then ");
		sql.append("           '美容护肤' ");
		sql.append("          when 3 then ");
		sql.append("           '手表饰品' ");
		sql.append("          when 4 then ");
		sql.append("           '手机数码' ");
		sql.append("          when 5 then ");
		sql.append("           '家用电器' ");
		sql.append("          when 6 then ");
		sql.append("           '服装箱包' ");
		sql.append("          when 7 then ");
		sql.append("           '食品保健' ");
		sql.append("          when 8 then ");
		sql.append("           '户外运动' ");
		sql.append("          when 9 then ");
		sql.append("           '母婴用品' ");
		sql.append("          when 10 then ");
		sql.append("           '图书音像' ");
		sql.append("          else ");
		sql.append("           '' ");
		sql.append("        end as operateName, ");
		sql.append("        '' as product, ");
		sql.append("        region.region_name areaName, ");
		sql.append("        '' as saleArea, ");
		sql.append("        store.link_name, ");
		sql.append("        store.link_phone, ");
		sql.append("        store.email, ");
		sql.append("        store.bs_manager_name, ");
		sql.append("        store.bs_manager_phone, ");
		sql.append("        store.fc_manager_name, ");
		sql.append("        store.fc_manager_phone, ");
		sql.append("        to_char(to_date(store.contract_begintime, 'yyyy-MM-dd HH24:mi:ss'), ");
		sql.append("                'yyyy-MM-dd hh24:mi:ss') || '至' || ");
		sql.append("        to_char(to_date(store.contract_endtime, 'yyyy-MM-dd HH24:mi:ss'), ");
		sql.append("                'yyyy-MM-dd hh24:mi:ss') as yxq, ");
		sql.append("        store.address ");

		sql.append(" from t_store store ").append(" left join t_sys_region region on store.area_code = region.region_code ");
		sql.append(" where 1 = 1 ");

		if (store.getId() != null) {
			sql.append(" and store.id = " + store.getId());
		}

		if (StringUtils.isNotEmpty(store.getName())) {
			sql.append(" and store.name like '%" + store.getName().trim() + "%' ");
		}

		if (StringUtils.isNotEmpty(store.getAddress())) {
			sql.append(" and store.address like '%" + store.getAddress().trim() + "%' ");
		}

		if (StringUtils.isNotEmpty(store.getStatus() == null ? "" : store.getStatus().toString())) {
			sql.append(" and store.status =  " + store.getStatus());
		}

		if (StringUtils.isNotEmpty(store.getBeginTime())) {
			sql.append(" and store.create_time >= '" + store.getBeginTime() + "000000' ");
		}
		if (StringUtils.isNotEmpty(store.getEndTime())) {
			sql.append(" and store.create_time <= '" + store.getEndTime() + "235959' ");
		}
		if (store.getIsValid() != null) {
			sql.append(" and store.is_valid =  " + store.getIsValid());
		}
		if (StringUtils.isNotEmpty(store.getAreaName())) {
			sql.append(" and region.region_name like '%" + store.getAreaName().trim() + "%' ");
		}
		sql.append(" order by store.create_time desc ");

		System.out.println(sql.toString());

		String[] head = { "商户名称", "经营品类", "经营产品", "归属地市", "可销售范围", "业务联系人", "联系电话", "邮箱", "应急联系人", "第二联系人电话", "财务联系人", "财务联系人电话", "合同期限", "通信地址" };
		ExcelExportUtil.exportExcel(dbHelper.getConn(), head, "storeInfo_", sql.toString(), request, response);
	}



	
	public boolean checkExit(Store store){
		List<Store> cclifeStores = storeDao.findByName(store.getName());
		return cclifeStores.isEmpty();
	}
	/**
	 * 新增商户
	 * 
	 * @param store
	 *            商户
	 * @param ext
	 *            合同名称
	 * @param is
	 * @throws Exception
	 */
	@Transactional
	public void saveStoreAndFile(Store store) throws RuntimeException {
		try {
			this.saveStore(store);
		}
		catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * @throws SQLException 
	 * @Title: 根据当前登陆帐号，返回对应的商户ID 
	 * <br>
	 * @param userCode
	 * @return    
	 * @throws
	 */
	public Long findStoreIdByUserCode(Long userID) throws SQLException {
		String sql = "select t.id from t_store t where t.is_valid=1 and t.sys_user_id=?";
		String[] storeId = dbHelper.getArray(sql, new Object[]{userID});
		if(storeId != null && storeId.length>0 && StringUtils.isNotBlank(storeId[0])) {
			return Long.parseLong(storeId[0]);
		}
		return null;
	}
	
	
	/**
	 * 修改商户
	 * 
	 * @param store
	 *            商户
	 * @throws Exception
	 */
	@Transactional
	public void updateStoreAndFile(Store store, Store oldStore) throws Exception {
		oldStore.setAddress(store.getAddress());
		oldStore.setAreaCode(store.getAreaCode());
		oldStore.setEmail(store.getEmail());
		oldStore.setLinkName(store.getLinkName());
		oldStore.setLinkPhone(store.getLinkPhone());
		oldStore.setName(store.getName());
		oldStore.setStatus(0l);
		oldStore.setImgPath(store.getImgPath());
		try {
			this.saveStore(oldStore);
		}
		catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	
}
